//! @Author: DengLibin
//! @Date: Create in 2024-11-05 10:02:05
//! @Description: sqlite

use anyhow::Ok;
use chrono::{DateTime, NaiveDateTime, Utc};
use serde::{Deserialize, Serialize};

use serde_json::{json, Value};
use sqlx::{
    sqlite::{SqliteArguments, SqliteConnectOptions, SqliteRow},
    FromRow, Pool, Sqlite, SqlitePool,
};

use crate::sys::global::{to_global_result, GlobalResult};
use my_trait::{MustHaveFieldType, MustHaveTableName, Struct2Map};

#[derive(Debug, FromRow, Serialize, Deserialize)]
pub struct SqliteUser {
    pub id: i64,
    pub name: Option<String>,
}

/// @Author: DengLibin
/// @Date: Create in 2024-11-05 10:15:28
/// @Description: 获取sqlite连接池
/// @params: url 链接地址: sqlite:test.db
pub async fn get_conn_pool(url: &str) -> GlobalResult<SqlitePool> {
    let options: SqliteConnectOptions = to_global_result(url.parse())?;
    let options = options.create_if_missing(true);

    let r: Result<SqlitePool, sqlx::Error> = SqlitePool::connect_with(options).await;
    to_global_result(r)
}

/// @Author: DengLibin
/// @Date: Create in 2024-11-05 10:36:26
/// @Description: 创建表
pub async fn create_tabale_demo(pool: &SqlitePool) -> GlobalResult<()> {
    let mut tx = to_global_result(pool.begin().await)?;
    let sql = r#"CREATE TABLE USER(id integer, name text)"#;

    let query = sqlx::query::<Sqlite>(sql);

    let r = query.execute(&mut *tx).await;
    if let Err(err) = r {
        // 回滚事务
        to_global_result(tx.rollback().await)?;

        return Err(anyhow::anyhow!(err.to_string()));
    } else {
        //提交
        to_global_result(tx.commit().await)?;
    }
    Ok(())
}
/// @Author: DengLibin
/// @Date: Create in 2025-02-07 11:51:10
/// @Description: 建表
pub async fn create_tabale(create_table_sql: &str, pool: &SqlitePool) -> GlobalResult<()> {
    let mut tx: sqlx::Transaction<'_, Sqlite> = to_global_result(pool.begin().await)?;

    let query = sqlx::query::<Sqlite>(create_table_sql);

    let r = query.execute(&mut *tx).await;
    if let Err(err) = r {
        // 回滚事务
        to_global_result(tx.rollback().await)?;
        return Err(anyhow::anyhow!(err.to_string()));
    } else {
        //提交
        to_global_result(tx.commit().await)?;
    }
    Ok(())
}

/// @Author: DengLibin
/// @Date: Create in 2024-11-05 10:41:12
/// @Description: 添加
pub async fn add_demo(pool: &SqlitePool) -> GlobalResult<()> {
    let mut tx = to_global_result(pool.begin().await)?;
    let sql = r#"INSERT INTO USER(id , name)VALUES($1, $2)"#;

    let mut query = sqlx::query::<Sqlite>(sql);
    query = query.bind(1).bind("张三");

    let r = query.execute(&mut *tx).await;
    if let Err(err) = r {
        // 回滚事务
        to_global_result(tx.rollback().await)?;
        return Err(anyhow::anyhow!(err.to_string()));
    } else {
        //提交
        to_global_result(tx.commit().await)?;
    }
    Ok(())
}

/// @Author: DengLibin
/// @Date: Create in 2025-02-07 11:34:11
/// @Description: 批量添加
/// @table_name: 表名
/// @colums: 字段
/// @rows: 数据
pub async fn insert_batch<E>(rows: &Vec<E>, pool: &SqlitePool) -> GlobalResult<()>
where
    E: MustHaveTableName + MustHaveFieldType + Struct2Map,
{
    if rows.is_empty() {
        return Ok(());
    }
    let mut tx = to_global_result(pool.begin().await)?;
    for row in rows {
        let (fields, types, params) = super::super::field_type_vlues(row);

        let mut values = vec![];
        for i in 0..fields.len() {
            values.push(format!("${}", i + 1));
        }
        let sql = format!(
            "insert into {}({})values ({})",
            E::table_name(),
            fields.join(","),
            values.join(",")
        );

        let mut query = sqlx::query::<Sqlite>(&sql);
        //绑定更新参数
        query = bind_value(&params, &types, query)?;

        let r = query.execute(&mut *tx).await;
        if let Err(e) = r {
            // 回滚事务
            to_global_result(tx.rollback().await)?;
            return Err(anyhow::anyhow!(e.to_string()));
        }
    }

    //提交
    to_global_result(tx.commit().await)?;

    Ok(())
}

pub async fn insert_batch_data(
    table_name: &str,
    columns: &Vec<String>,
    rows: &Vec<Vec<Value>>,
    pool: &SqlitePool,
) -> GlobalResult<()> {
    if rows.is_empty() {
        return Ok(());
    }
    let mut tx = to_global_result(pool.begin().await)?;
    for row in rows {
        let mut values = vec![];
        for i in 0..columns.len() {
            values.push(format!("${}", i + 1));
        }
        let sql = format!(
            "insert into {}({})values ({})",
            table_name,
            columns.join(","),
            values.join(",")
        );

        let mut query = sqlx::query::<Sqlite>(&sql);
        //绑定更新参数
        query = bind_value_data(row, query)?;

        let r = query.execute(&mut *tx).await;
        if let Err(e) = r {
            // 回滚事务
            to_global_result(tx.rollback().await)?;
            return Err(anyhow::anyhow!(e.to_string()));
        }
    }

    //提交
    to_global_result(tx.commit().await)?;

    Ok(())
}

/// @Author: DengLibin
/// @Date: Create in 2024-11-05 11:43:38
/// @Description: 查询
pub async fn select_demo(pool: &SqlitePool) -> GlobalResult<()> {
    let sql = r#"SELECT * FROM USER"#;

    let query = sqlx::query_as::<Sqlite, SqliteUser>(sql);
    let all = to_global_result(query.fetch_all(pool).await)?;
    for row in all {
        println!("{:?}", row);
    }
    Ok(())
}

/// @Author: DengLibin
/// @Date: Create in 2025-02-07 16:42:56
/// @Description: 批量查询
pub async fn query_sql<E>(sql: &str, pool: &Pool<Sqlite>) -> GlobalResult<Vec<E>>
where
    E: Serialize + Send + Unpin + for<'r> FromRow<'r, SqliteRow>,
{
    // 执行查询
    let query = sqlx::query_as::<Sqlite, E>(sql);
    to_global_result(query.fetch_all(pool).await)
}

/// @Author: DengLibin
/// @Date: Create in 2024-07-19 14:40:00
/// @Description: 绑定参数
fn bind_value<'q>(
    params: &Vec<Value>, //参数
    types: &Vec<String>, //参数类型(和参数一一对应)
    mut query: sqlx::query::Query<'q, Sqlite, SqliteArguments<'q>>,
) -> GlobalResult<sqlx::query::Query<'q, Sqlite, SqliteArguments<'q>>> {
    let mut index = 0;
    //绑定值
    for value in params {
        let type_s = types[index].as_str();
        match value {
            Value::Null => match type_s {
                "Option<DateTime<Utc>>" => query = query.bind::<Option<DateTime<Utc>>>(None),
                "Option<NaiveDateTime>" => query = query.bind::<Option<DateTime<Utc>>>(None),
                "Option<i64>" => query = query.bind::<Option<i64>>(None),
                "Option<i32>" => query = query.bind::<Option<i32>>(None),
                "Option<f64>" => query = query.bind::<Option<f64>>(None),
                "Option<f32>" => query = query.bind::<Option<f32>>(None),
                "Option<String>" => query = query.bind::<Option<String>>(None),

                _ => {
                    return Err(anyhow::anyhow!(format!("Null类型错误:{}", type_s)));
                }
            },
            Value::Bool(b) => query = query.bind::<bool>(*b),
            Value::Number(n) => match type_s {
                "Option<i64>" => query = query.bind::<Option<i64>>(Some(n.as_i64().unwrap())),
                "Option<i32>" => {
                    query = query.bind::<Option<i32>>(Some(n.as_i64().unwrap() as i32))
                }
                "Option<f64>" => query = query.bind::<Option<f64>>(Some(n.as_f64().unwrap())),
                "Option<f32>" => {
                    query = query.bind::<Option<f32>>(Some(n.as_f64().unwrap() as f32))
                }
                "i64" => query = query.bind::<i64>(n.as_i64().unwrap()),
                "i32" => query = query.bind::<i32>(n.as_i64().unwrap() as i32),
                "f64" => query = query.bind::<f64>(n.as_f64().unwrap()),
                "f32" => query = query.bind::<f32>(n.as_i64().unwrap() as f32),
                _ => {
                    return Err(anyhow::anyhow!(format!("Number类型错误:{}", type_s)));
                }
            },
            Value::String(s) => match type_s {
                "String" => query = query.bind::<String>(s.to_string()),
                "Option<String>" => query = query.bind::<Option<String>>(Some(s.to_string())),
                "DateTime<Utc>" => {
                    let date_r = s.parse::<DateTime<Utc>>();
                    let date = to_global_result(date_r)?;
                    query = query.bind::<DateTime<Utc>>(date);
                }
                "Option<DateTime<Utc>>" => {
                    let date_r = s.parse::<DateTime<Utc>>();
                    let date = to_global_result(date_r)?;
                    query = query.bind::<Option<DateTime<Utc>>>(Some(date));
                }
                "NaiveDateTime" => {
                    let date_r = s.parse::<NaiveDateTime>();
                    let date = to_global_result(date_r)?;
                    query = query.bind::<NaiveDateTime>(date);
                }
                "Option<NaiveDateTime>" => {
                    let date_r = s.parse::<NaiveDateTime>();
                    let date = to_global_result(date_r)?;
                    query = query.bind::<Option<NaiveDateTime>>(Some(date));
                }

                _ => {
                    return Err(anyhow::anyhow!(format!("String类型错误:{}", type_s)));
                }
            },
            _ => {}
        }
        index += 1;
    }
    Ok(query)
}
fn bind_value_data<'q>(
    params: &Vec<Value>, //参数
    mut query: sqlx::query::Query<'q, Sqlite, SqliteArguments<'q>>,
) -> GlobalResult<sqlx::query::Query<'q, Sqlite, SqliteArguments<'q>>> {

    //绑定值
    for value in params {
        match value {
            Value::Null => query = query.bind::<Option<String>>(None),
            Value::Bool(b) => query = query.bind::<bool>(*b),
            Value::Number(n) => query = query.bind::<Option<i64>>(Some(n.as_i64().unwrap())),

            Value::String(s) => query = query.bind::<Option<String>>(Some(s.to_string())),

            _ => {
                return Err(anyhow::anyhow!(format!("类型错误:{}", value)));
            }
        }
    }
    Ok(query)
}
